<?

/**
 * Model class
 *
 * @author
 * @version 1.0
*/
class Model extends Core {

	/* Class options */
	public $defaults = array(
		'table' => NULL,
		'page' => 1,
		'limit' => 10,
		'order' => NULL,
		'key' => NULL,
		'fields' => NULL,
		'filters' => NULL,
		'fieldset' => NULL
	);
	
	public $options = array();
	
	/* Class constants */
	static $instance;
	private $count = 0;
	private $pages = 0;
	private $default_field_types = array(
		'char' => 'text',
		'varchar' => 'text',
		'tinytext' => 'text',
		'text' => 'textarea',
		'blob' => 'textarea',
		'mediumtext' => 'textarea',
		'mediumblob' => 'textarea',
		'longtext' => 'textarea',
		'longblob' => 'textarea',
		'tinyint' => 'text',
		'smallint' => 'text',
		'mediumint' => 'text',
		'int' => 'text',
		'bigint' => 'text',
		'float' => 'text',
		'double' => 'text',
		'decimal' => 'text',
		'date' => 'date',
		'datetime' => 'date',
		'timestamp' => 'text',
		'time' => 'date',
		'enum' => 'select',
		'enumeration' => 'radio',
		'set' => 'checkbox'
	);


	/**
	 * Class constructor
	 * 
	 * @param array $options
	 */
	function __construct ($options = null) {
		parent::__construct();
		// Set defaults
		$this->options($this->defaults);
		if (is_array($options)) {
			// Set class options
			$this->options($options);
		} else {
			// Set minimum options by table name
			$this->options(array("table" => (string)$options));
		}
		// Set column info
		$this->fields();
	}
	
	
	/**
	 * Test method
	 * @param string $text
	 */
	function test($text) {
		echo "Testing:" . $text;
	}


	/**
	 * Sets class options
	 * 
	 * @param array $options
	 * @return array $options
	 */
	/* 
	public function options ($options = null) {
		$getter = self::$options = parent::options(self::$options, $options);
		if (is_string($options))
			return $getter[$options];
		return $getter;
	}
	*/

	/**
	 * Returns a class instance
	 * 
	 * @return object
	 */
	public static function getInstance() {
		if (!self::$instance instanceof self)
			self::$instance = new self;
		return self::$instance;
	}

	

/* CRUD methods
***************************************************************************/	
	
	/**
	 * Adds a record to the database
	 * 
	 * @param array $item Data to store in the new record
	 * @return array $result Message with error or item ID
	 */
	public function add ($item) {

		// Get or instance DB access
		$db = ($this)? $this->db : ez_sql::getInstance();
		
		// Get options
		$options = ($this instanceof self)? $this->options() : self::options();

		// Get the fields
		$fieldset = ($this instanceof self)? $this->fieldset($options['action']) : self::fieldset($options['action']);
		
		// Order data
		foreach ($fieldset as $field) {
			if ($_item[$field['field']]) {
				$item[$field['field']] = mysql_real_escape_string($_item[$field['field']]);
			}
		}
		
		// Create record
		if ($id_item = $db->insert($options['table'], $item)) {
			$result = array(
				'success' => true,
				'id' => $id_item,
				'message' => 'added'
			);
		} else {
			$result = array(
				'success' => false,
				'message' => 'error inserting into database'
			);
		}
		
		// Return
		return $result;
		
	}

	/**
	 * Removes a record from database
	 * 
	 * @param integer $id ID of item to remove
	 * @return array $result Message with error or item ID
	 */
	public function remove ($id) {

		// Sanitaze
		$id_item = mysql_real_escape_string($id);

		// Get or instance DB access
		$db = ($this)? $this->db : ez_sql::getInstance();

		// Get options
		$options = ($this instanceof self)? $this->options() : self::options();

		// Update record
		if ($db->query("DELETE FROM " . $options['table'] . " WHERE " . $options['key'] . " = '$id_item' LIMIT 1")) {
			$result = array(
				'success' => true,
				'id' => $id_item,
				'message' => 'deleted'
			);
		} else {
			$result = array(
				'success' => false,
				'id' => $id_item,
				'message' => 'error removing from database'
			);
		}

		// Return
		return $result;

	}

	/**
	 * Updates a record from database
	 * 
	 * @param integer $id ID of item to update
	 * @param array $data Data to update on the item
	 * @return array $result Message with error or item ID
	 */
	public function update ($id, $data) {
	
		// Sanitaze
		$id_item = mysql_real_escape_string($id);

		// Get or instance DB access
		$db = ($this)? $this->db : ez_sql::getInstance();
		
		// Get options
		$options = ($this instanceof self)? $this->options() : self::options();

		// Get the fields
		$fieldset = ($this instanceof self)? $this->fieldset($options['action']) : self::fieldset($options['action']);
		
		// Order data
		foreach ($fieldset as $field) {
			if ($_item[$field['field']]) {
				$data[$field['field']] = mysql_real_escape_string($_item[$field['field']]);
			}
		}
		
		// Update record
		if ($db->update($options['table'], array_merge(array($options['key'] => $id_item), $data))) {
			$result = array(
				'success' => true,
				'id' => $id_item,
				'message' => 'updated'
			);
		} else {
			$result = array(
				'success' => false,
				'id' => $id_item,
				'message' => 'error updating from database'
			);
		}

		// Return
		return $result;

	}
	

	/**
	 * Read records from database
	 * 
	 * @param array $options
	 * @return array $result
	 */
	public function get ($options = null) {
	
		// Get or instance DB access
		$db = ($this)? $this->db : ez_sql::getInstance();
		
		// Get IDs
		if ($options["id"])
			$id = $options["id"];
			
		// Get the action
		$action = $options['action'];
		
		// Save fieldset in options
		if ($options["fieldset"]) {
			$this->options['fieldset'][$action] = $options["fieldset"];
		}
		
		// Merge actual options with stored options
		// $options = ($this instanceof self)? $this->options($options) : self::options($options);
		$options = array_merge($this->options, $options);
		
		// Set all the columns and tables needed
		$editable = ($this instanceof self)? $this->columns($action, $options['table']) : self::columns($action, $options['table']);
		$fieldset = $editable['fieldset'];
		$tables = $editable['tables'];
		$join = $editable['join'];
		
		/* Write the SQL query
		*******************************************/
		
		// Check fieldset
		if (!$fieldset) {
			echo "<p>Magnet error: No fields stored in class.</p>";
			return false;
		}
		
		// Write the fields
		foreach ($fieldset as $field)
			$columns[] = $field['table'] . '.' .$field['field'];
		$columns = join(', ', $columns);
		
		// Write the joins
		if ($join)
			foreach ($join as $table => $relation)
				$joins .= ' LEFT JOIN ' . $table . ' ' . $table .  ' ON ' . $relation;		
		
		// Write the order by
		$options['order'] = ($options['order'])? $options['order'] : array($options['table'] . '.' . $options['key'], 'asc');
		$order = join(' ', $options['order']);
		
		// Add the table if it is not defined
		if (strpos($order, '.') === false)
			$order = $options['table'] . '.' . $order;
			
		// Restrict the items to get by ID
		if ($id) {
			$rel = $options['table'] . '.' . $options['key'] . ' = ';
			if (!is_array($id))
				$id = array($id);
			$keys .= " AND " . $rel . join($id, ' OR ' . $rel);
		}
		
		// Add optional filters
		if (is_array($options["filters"]))
			foreach ($options["filters"] as $key => $filter)
				if ($filter)
					$filters .= " AND " . $filter['column'] . " " . $filter['operator'] . " '%" . $filter['value'] . "%'";
					
		// Set the initial item
		$initial = ($options['page'] - 1) * $options['limit'];
		
		// Create the basic query
		$query = "SELECT SQL_CALC_FOUND_ROWS " . $columns . " FROM " . $options['table'] . ' ' . $options['table'] . $joins . " WHERE 1=1 " . $keys . $filters;
		
		// Add order and limit
		$query .= " ORDER BY " . $order . ", " . $options['key'] . " LIMIT " . $initial . ", " . $options['limit'];
		
		// Get items from database
		$_items = $db->get_results($query);
		
		// echo $query;
		// $db->debug();
		
		// Order the items
		if ($_items) {
			foreach ($_items as $key_item => $value_item) {
				$_item[$options['key']] = current($value_item);
				foreach ($fieldset as $key => $value) {
					if (!$value['hide'])
						$_item[$value['field']] = stripslashes($_items[$key_item][$value['field']]);
				}
				$items[$_item[$options['key']]] = $_item;
			}
		}
		
		// Get the amount of records
		$count = (int)$db->get_var('SELECT FOUND_ROWS()');
		if ($this instanceof self)
			$this->count = $count;	

		// Get the amount of pages
		$pages = ($this instanceof self)? $this->pages() : self::pages($count, $options['limit']);
		
		// Prepare array of results
		$result['data'] = $items;
		
		// (Optional) Navigation		
		if ($action == 'browse' && !$id) {
			// Create the listing paging buttons
			$paging = Helpers::paginate($options['url']['without_paging'], $options['limit'], (int)$count, $options['page']);
			$paging = array(
				'pages' => $pages,
				'records' => (int)$count,
				'current' => $options['page'],
				'limit' => $options['limit'],
				'from' => $paging['from'],
				'to' => $paging['to'],
				'buttons' => $paging['buttons']
			);
			// Set current list data
			$this->session->set(
				'list-' . $options["table"],
				array(
					'page' => $options['page'],
					'limit' => $options['limit'],
					'order' => $options['order'],
					'filters' => $options['filters'],
					'url' => 'browse/' . $options['url']['back']
				)
			);
		} else if ($count === 1) {
			// Get last list data
			$state = $this->session->get('list-' . $options["table"]);
			$arrange = $state["order"];
			$back = $state['url'];
			$paging['buttons']["back"] = array("text" => "Back", "url" => $back);
			// Get next / previous ID records
			$cursor = current($items);
			$order_field = explode('.', current($arrange));
			$order_field = end($order_field);
			$val = $cursor[$order_field];
			$current = $cursor[$options['key']];
			$primary = $options['table'] . "." . $options['key'];
			$look = $arrange[0];
			$way = ($arrange[1] == 'asc')? 'desc' : 'asc';
			$base = "SELECT " . $primary . " FROM " . $options['table'] . ' ' . $options['table'] . $joins . " WHERE 1=1 AND " . $look . " = '" . $val . "' AND " . $primary . " > " . $current . " OR " . $look . " > '" . $val . "'";
			$record['next'] = $base . " ORDER BY " . $look . " " . $arrange[1] . ", " . $primary . " LIMIT 1";
			$record['previous'] = str_replace('>', '<', $base) . " ORDER BY " . $look . " " . $way . ", " . $primary . " desc LIMIT 1";
			$record = array_reverse($record);
			foreach ($record as $key => $query) {
				$n = $db->get_var($query);
				if ($n) {
					$paging['buttons'][$key] = array('text' => ucfirst($key), 'number' => $n);
				}
			}
		}
		if ($paging)
			$result['paging'] = $paging;
			
			
		// (Optional) Fields info
		$result['info'] = array(
			'table' => $options['table'],
			'key' => $options['key'],
			'order' => $options['order'],
			'columns' => $fieldset,
		);
		if ($options['filters'])
			$result['info']['filters'] = $options['filters'];
			
		
		//printer($result);
		//exit;
		
		return $result;
		
	}
	


/* Database methods
***************************************************************************/
	
	/**
	 * Gets all the table names
	 * 
	 * @param array $base Database name
	 * @return array $tables Table names
	 */	
	public function tables ($base) {
	
		// Sanitaze
		$base = mysql_real_escape_string($base);
	
		// Get or instance DB access
		$db = ($this)? $this->db : ez_sql::getInstance();
		
		// Get the tables
		$_tables = $db->get_results("SHOW TABLES FROM " . $base);
		
		// Order data
		foreach($_tables as $table)
			$tables[current($table)] = array('table' => current($table));

		// Return
		return $tables;
		
	}


	/**
	 * Gets database structure and field info
	 * 
	 * @param string $action
	 * @param string $table
	 * @return array $columns
	 */		
	public function columns ($action, $table) {
	
		// Database object
		$db = ($this)? $this->db : ez_sql::getInstance();
		
		// Get the tables
		$tables[] = $table;
		
		// Get the fields
		$fieldset = ($this instanceof self)? $this->fieldset($action) : self::fieldset($action);
		
		// Exit if no fields are found
		if (!$fieldset) {
			echo "<p>Magnet error: No fields stored in class.</p>";
			return false;
		}
		
		// Order all the fields
		foreach ($fieldset as $name => $value) {
		
			// Set the table
			$fieldset[$name]['table'] = ($value['table']) ? $value['table'] : $table;
			
			// Set the field name
			// $fieldset[$name]['field'] = $name;
		
			// Set the alias
			$fieldset[$name]['alias'] = ($value['alias']) ? $value['alias'] : $name;

			// Set the join
			if ((isset($value['join']))) {
				$tables[] = $value['join'];
				$index = ($value['index']) ? ($value['index']) : $value['field'];
				$join[$value['join']] = $table . '.' . $index . ' = ' . $value['join'] . '.' . $value['on'];
				$fieldset[$name]['table'] = $value['join'];
				$fieldset[$name]['field'] = $value['get'];
			}
			
		}
		
		// Get structure
		foreach ($tables as $table)
			$structure[$table] = ($this instanceof self)? $this->fields($table) : self::fields($table);
		
		// Add structure info to the fields
		foreach ($fieldset as $field_key => $field_value)
			$fieldset[$field_key]['structure'] = $structure[$field_value['table']][$field_value['field']];
			
		// Overwrite the structure for selects
		foreach ($fieldset as $field_key => $field_value) {
			if ($field_value['select']) {
				$fieldset[$field_key]['structure']['input'] = 'select';
				$selectables = $db->get_results("SELECT " . $field_value['on'] . ", " . $field_value['get'] . " FROM " . $field_value['join'] . " ORDER BY " . $field_value['get']);
				foreach ($selectables as $selectable) {
					$fieldset[$field_key]['structure']['options'][$selectable[$field_value['on']]] = stripslashes($selectable[$field_value['get']]);
				}
			}
		}
		
		// Return
		return array('fieldset' => $fieldset, 'tables' => $tables, 'join' => $join);
		
	}

	/**
	 * Gets allowed fields for the choosen action
	 * 
	 * @param string $action Action name
	 * @return array $fieldset Action fields
	 */		
	public function fieldset ($action) {
	
		// Get options
		$options = ($this instanceof self)? $this->options : self::options;
		
		// Get the primary key
		$fieldset[$options['key']] = array('table' => $options['table'], 'field' => $options['key'], 'hide' => true);
		
		//printer($this);
		//exit;
		
		// Exit if fields are not stored
		if (!$options['fields']) {
			echo "<p>Magnet error: No fields stored in class [fieldset('$action') function].</p>";
			return false;
		}
		
		// Organize the required fields
		$fields = ($options["fieldset"][$action])? $options["fieldset"][$action] : array_keys($options['fields']);
		
		foreach ($fields as $key => $value) {
			if (is_array($value)) {
				$fieldset[$key] = $value;
				$fieldset[$key]['field'] = $key;
				$fieldset[$key]['byfieldset'] = 1;
			} else {
				$fieldset[$value]['field'] = $value;  // todo: change if we turn to true the field definition
			}
		}
		
		// Return
		return $fieldset;
		
	}	

	
	
	/**
	 * Gets column names and primary key from the choosen table,
	 * and stores them in the class options
	 * 
	 * @param string $table Table name
	 * @return array $fields Column names of the table
	 */	
	public function fields ($table = null) {
	
		// Sanitaze
		$table = mysql_real_escape_string($table);
	
		// Get or instance DB access
		$db = ($this)? $this->db : ez_sql::getInstance();
		
		// Obtengo las options
		$options = ($this)? $this->options : self::$options;
		
		// Set table
		$table = ($table)? $table : $options['table'];
		
		// If no table is defined, exit
		if (!$table) {
			echo "<p>Magnet error: No database defined [fields() function].</p>";
		}
	
		// Get the fields
		$_fields = $db->get_results("SHOW fields FROM " . $table);
		
		if (!$_fields) {
			echo "<p>Magnet error: No fields found in table: $table [fields() function].</p>";
		} else {
		
			// Walk all of them
			foreach ($_fields as $field) {
			
				// Reset
				$_field = null;
				// Get the name
				$_field['name'] = $field['Field'];
				
				// Get the type
				if (preg_match('/([a-z]+)/i', $field['Type'], $matches))
					$_field['type'] = $matches[1];
					
				// Get the size	
				if (preg_match('/\(([0-9]+)\)/i', $field['Type'], $matches))
					$_field['size'] = $matches[1]; 
					
				// Get the options
				if (in_array($_field['type'], array('enumeration', 'enum', 'set')))
					if (preg_match('/\(([\w,\']+)\)/i', $field['Type'], $matches))
						$_field['options'] = explode(',', str_replace("'", "", $matches[1]));
					
				// Set the form field type
				$_field['input'] = ($this)? $this->default_field_types[$_field['type']] : self::$default_field_types[$_field['type']];
			
				// Fetch the fields
				$fields[$field['Field']] = $_field;
				
				// Save the key
				if ($field['Key'] == 'PRI') {
					$options['key'] = $field['Field'];
				}

			}
		
		}
		
		// Save to the options
		$options['fields'] = $fields;
		$options = ($this instanceof self)? $this->options($options) : self::options($options);
		
		//printer($this);
		//echo "<hr>";
		
		// Return
		return $fields;

	}
	



/* Extra methods
***************************************************************************/

	/**
	 * Returns the total records number of the query
	 * 
	 * @return integer Cantidad de resultados
	 */
	public function count () {
		return $this->count;
	}
	
	/**
	 * Calculates the total pages number of the query
	 * 
	 * @param array $count Total records
	 * @param array $limit Records by page
	 * @return integer $pages Total number of pages
	 */
	public function pages ($count = null, $limit = null) {

		$limit = (!$limit)? $this->options['limit'] : $limit;
		$count = (!$count && $this instanceof self)? $this->count() : $count;

		// Zero pages
		if ($limit == 0)
			return 0;
		
		// Return
		$pages = ceil($count/$limit);
		return $pages;
	}
	
	
	
/* Shotcuts
***************************************************************************/
	
	// Shortcut of get() for getting many items
	public function browse ($options = null) {
		$options["action"] = "browse";
		$data = ($this)? $this->get($options) : self::get($options);
		return $data;
	}
	
	// Shortcut of get() for getting one item
	public function details ($options = null) {
		if (!is_array($options))
			$options = array("id" => $options);
		$options["action"] = "details";
		$data = ($this)? $this->get($options) : self::get($options);
		return $data;
	}	
	
	// Shortcut of get() for getting one item to edit
	public function edit ($options = null) {
		if (!is_array($options))
			$options = array("id" => $options);
		$options["action"] = "edit";
		$data = ($this)? $this->get($options) : self::get($options);
		return $data;
	}	
	
	

/* Method aliases
***************************************************************************/

/*	
	public function __call($func, $args) {
        switch ($func) {
            case 'list':
                return $this->ls((isset($args[0]))? $args[0]: null);
            break;
            case 'unset':
                return $this->rm($args[0]);
            break;
            default:
                trigger_error("Call to undefined method ".__CLASS__."::$func()", E_USER_ERROR);
            die();
        }
    }
	
*/

}

?>
